package com.liang.dao.provider;

import com.liang.dao.BaseDao;
import com.liang.pojo.Provider;
import com.mysql.cj.util.StringUtils;
import org.junit.Test;

import javax.xml.crypto.Data;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ProviderImpl implements ProviderDao {

    /**
     * 获取供应商数量
     *
     * @param connection
     * @param proCode
     * @param proName
     * @return
     */
    @Override
    public Integer getCount(Connection connection, String proCode, String proName) {
        Integer count = 0;
        PreparedStatement pr = null;
        ResultSet rs = null;

        if (connection != null) {
            try {
                StringBuffer stringBuffer = new StringBuffer();
                ArrayList<Object> params = new ArrayList<>();
                stringBuffer.append("select count(1) as count from smbms_provider");
                if (!StringUtils.isNullOrEmpty(proName) && !StringUtils.isNullOrEmpty(proCode)) {
                    stringBuffer.append(" where proCode like ? and proName like ?");
                    params.add("%" + proCode + "%");
                    params.add("%" + proName + "%");
                } else if (!StringUtils.isNullOrEmpty(proCode)) {
                    stringBuffer.append(" where proCode like ?");
                    params.add("%" + proCode + "%");
                } else if (!StringUtils.isNullOrEmpty(proName)) {
                    stringBuffer.append(" where proName like ?");
                    params.add("%" + proName + "%");
                }
                rs = BaseDao.execute(connection, pr, rs, stringBuffer.toString(), params.toArray());
                if (rs.next()) {
                    count = rs.getInt("count");
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                BaseDao.closeResource(rs, pr, connection);
            }
        }


        return count;
    }

    /**
     * 获取供应商分页列表
     * @param connection
     * @param proCode
     * @param proName
     * @param pageNo
     * @param pageSize
     * @return
     */

    @Override
    public List<Provider> getProviderList(Connection connection, String proCode, String proName, int pageNo, int pageSize) {
        List<Provider> list = new ArrayList<>();
        PreparedStatement pr = null;
        ResultSet rs = null;

        if (connection != null) {
            try {
                StringBuffer stringBuffer = new StringBuffer();
                ArrayList<Object> params = new ArrayList<>();
                stringBuffer.append("select * from smbms_provider");
                if (!StringUtils.isNullOrEmpty(proName) && !StringUtils.isNullOrEmpty(proCode)) {
                    stringBuffer.append(" where proCode like ? and proName like ?");
                    params.add("%" + proCode + "%");
                    params.add("%" + proName + "%");
                } else if (!StringUtils.isNullOrEmpty(proCode)) {
                    stringBuffer.append(" where proCode like ?");
                    params.add("%" + proCode + "%");
                } else if (!StringUtils.isNullOrEmpty(proName)) {
                    stringBuffer.append(" where proName like ?");
                    params.add("%" + proName + "%");
                }

                stringBuffer.append(" order by creationDate desc limit ?,?");
                int current = (pageNo - 1) * pageSize;
                params.add(current);
                params.add(pageSize);
                rs = BaseDao.execute(connection, pr, rs, stringBuffer.toString(), params.toArray());

                while (rs.next()) {
                    Provider provider = new Provider();
                    provider.setId(rs.getInt("id"));
                    provider.setProCode(rs.getString("proCode"));
                    provider.setProName(rs.getString("proName"));
                    provider.setProDesc(rs.getString("proDesc"));
                    provider.setProContact(rs.getString("proContact"));
                    provider.setProPhone(rs.getString("proPhone"));
                    provider.setProAddress(rs.getString("proAddress"));
                    provider.setProFax(rs.getString("proFax"));
                    provider.setCreatedBy(rs.getInt("createdBy"));
                    provider.setModifyBy(rs.getInt("modifyBy"));
                    provider.setCreationDate(rs.getDate("creationDate"));
                    provider.setModifyDate(rs.getDate("modifyDate"));

                    list.add(provider);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                BaseDao.closeResource(rs, pr, connection);
            }
        }


        return list;
    }

    /**
     * 获取供应商详情数据
     * @param connection
     * @param id
     * @return
     */
    @Override
    public Provider getProviderView(Connection connection, Integer id) {
        PreparedStatement pr = null;
        ResultSet rs = null;
        Provider providerView = new Provider();
        if (connection != null) {
            String sql = "select * from smbms_provider where id=?";
            Object[] params = {id};
            try {
                rs = BaseDao.execute(connection, pr, rs, sql, params);
                if (rs.next()) {
                    providerView.setId(rs.getInt("id"));
                    providerView.setProCode(rs.getString("proCode"));
                    providerView.setProName(rs.getString("proName"));
                    providerView.setProDesc(rs.getString("proDesc"));
                    providerView.setProContact(rs.getString("proContact"));
                    providerView.setProPhone(rs.getString("proPhone"));
                    providerView.setProAddress(rs.getString("proAddress"));
                    providerView.setProFax(rs.getString("proFax"));
                    providerView.setCreatedBy(rs.getInt("createdBy"));
                    providerView.setModifyBy(rs.getInt("modifyBy"));
                    providerView.setCreationDate(rs.getDate("creationDate"));
                    providerView.setModifyDate(rs.getDate("modifyDate"));
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                BaseDao.closeResource(rs, pr, connection);
            }
        }
        return providerView;
    }

    /**
     * 根据供应商编码查供应商信息
     * @param proCode
     * @return
     */
    @Override
    public Provider getProvideOfProCode(Connection connection,String proCode) {
        PreparedStatement pr = null;
        ResultSet rs = null;
        Provider providerView = null;
        if (connection != null) {
            String sql = "select * from smbms_provider where proCode=?";
            Object[] params = {proCode};
            try {
                rs = BaseDao.execute(connection, pr, rs, sql, params);
                if (rs.next()) {
                    providerView = new Provider();
                    providerView.setId(rs.getInt("id"));
                    providerView.setProCode(rs.getString("proCode"));
                    providerView.setProName(rs.getString("proName"));
                    providerView.setProDesc(rs.getString("proDesc"));
                    providerView.setProContact(rs.getString("proContact"));
                    providerView.setProPhone(rs.getString("proPhone"));
                    providerView.setProAddress(rs.getString("proAddress"));
                    providerView.setProFax(rs.getString("proFax"));
                    providerView.setCreatedBy(rs.getInt("createdBy"));
                    providerView.setModifyBy(rs.getInt("modifyBy"));
                    providerView.setCreationDate(rs.getDate("creationDate"));
                    providerView.setModifyDate(rs.getDate("modifyDate"));
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                BaseDao.closeResource(rs, pr, connection);
            }
        }
        return providerView;
    }

    /**
     * 修改供应商信息
     * @param connection
     * @param id
     * @param proCode
     * @param proName
     * @param proContact
     * @param proPhone
     * @param proAddress
     * @param proFax
     * @param proDesc
     * @return
     */
    @Override
    public Boolean updateProvider(Connection connection, Integer id, String proCode, String proName, String proContact, String proPhone, String proAddress, String proFax, String proDesc,Integer modifyBy, Date modifyDate) {
        Boolean flag = false;
        PreparedStatement pr=null;
        if(connection!=null){
            try {
                String sql = "update smbms_provider set proCode=?,proName=?,proContact=?,proPhone=?, proAddress=?,proFax=?,proDesc=?,modifyBy=?,modifyDate=? where id=?";
                Object[] params = {proCode, proName, proContact, proPhone, proAddress,proFax,proDesc,modifyBy,modifyDate,id};
                flag = BaseDao.execute(connection,pr,sql,params);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                BaseDao.closeResource(null,pr,connection);
            }

        }

        return flag;
    }

    /**
     * 新增供应商
     * @param connection
     * @param proCode
     * @param proName
     * @param proContact
     * @param proPhone
     * @param proAddress
     * @param proFax
     * @param proDesc
     * @param createdBy
     * @param creationDate
     * @return
     */
    @Override
    public Boolean insertProvider(Connection connection, String proCode, String proName, String proContact, String proPhone, String proAddress, String proFax, String proDesc, Integer createdBy, Date creationDate) {
        Boolean flag = false;
        PreparedStatement pr=null;
        if(connection!=null){
            try {
                String sql = "insert into smbms_provider (proCode, proName, proContact, proPhone, proAddress,proFax,proDesc,createdBy,creationDate) values (?,?,?,?,?,?,?,?,?)";
                Object[] params = {proCode, proName, proContact, proPhone, proAddress,proFax,proDesc,createdBy,creationDate};
                flag = BaseDao.execute(connection,pr,sql,params);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                BaseDao.closeResource(null,pr,connection);
            }

        }

        return flag;
    }

    @Override
    public Boolean deleteProvider(Connection connection, Integer id) {
        Boolean flag = false;
        PreparedStatement pr=null;
        if(connection!=null){
            try {
                String sql = "delete from smbms_provider where id=?";
                Object[] params = {id};
                flag = BaseDao.execute(connection,pr,sql,params);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }finally {
                BaseDao.closeResource(null,pr,connection);
            }
        }


        return flag;
    }
}
